using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Xml.XPath;
using System.Configuration;
namespace AdaptiveHelpDesk
{
    class StoredProcedures
    {
        SqlConnection con = null;
        SqlDataReader rdr = null;
        Hashtable hash = new Hashtable();
        private void openConnection()
        {
            try
            {
                ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[3];
                if (settings != null)
                {
                    con = new SqlConnection(settings.ConnectionString);
                    con.Open();
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }    
        }
        //calls the stored procedure that searches the customer by first name
        public Hashtable getCustomer(String by, String param)
        {
            try
            {
                openConnection();
                SqlCommand cmd = null;
                //old 1. First Name
                //old 1. Last Name
                //old 1. User ID
                if (by == "FirstName")
                {
                    cmd = new SqlCommand("GetCustomerByName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@fname", param));
                }
                else if (by == "LastName")
                {
                    cmd = new SqlCommand("GetCustomerBySurname", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@lname", param));
                }
                else if (by == "UserID")
                {
                    cmd = new SqlCommand("GetCustomerByUserID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@UserID", param));
                }
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    String custID = rdr["CustID"].ToString();
                    ArrayList values = new ArrayList();
                    values.Add(rdr["Firstname"].ToString());
                    values.Add(rdr["Surname"].ToString());
                    values.Add(rdr["EmailAdd"].ToString());
                    values.Add(rdr["Campus"].ToString());
                    values.Add(rdr["BuildingNo"].ToString());
                    values.Add(rdr["Room"].ToString());
                    values.Add(rdr["PhoneExt"].ToString());
                    //add to hash
                    hash.Add(custID, values);
                }
                return hash;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
        #region INSERT USER PROFILE
        public void InsertUserProfile(string UID, string fName, string lName, string date, string time, string stereotype)
        {
            try
            {
                openConnection();
                SqlCommand cmd = new SqlCommand("InsertUserProfile", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@UID", UID));
                cmd.Parameters.Add(new SqlParameter("@FName", fName));
                cmd.Parameters.Add(new SqlParameter("@LName", lName));
                cmd.Parameters.Add(new SqlParameter("@dLogged", date));
                cmd.Parameters.Add(new SqlParameter("@tLogged", time));
                cmd.Parameters.Add(new SqlParameter("@Stereotype", stereotype));
                cmd.ExecuteNonQuery();
            }
            catch
            {
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
        #endregion
        //just update display solution screen and timecompleted columns of User Profile
        #region UPDATE USER PROFILE
            public void updateUserModelDisplaySolScreenAndTimeCompleted(string UserID, string dateLogged, string timeLogged, string displaySolScreen, int TTime, string timeComplete)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("updateUserProfileDisplaySolandTimeCompleted", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                    cmd.Parameters.Add(new SqlParameter("@date", dateLogged));
                    cmd.Parameters.Add(new SqlParameter("@time", timeLogged));
                    cmd.Parameters.Add(new SqlParameter("@DisplaySolutionScreen", "YES"));
                    cmd.Parameters.Add(new SqlParameter("@TotalTaskTime", TTime));
                    cmd.Parameters.Add(new SqlParameter("@TimeComplete", timeComplete));
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
        #endregion
        #region getTotalTaskTimeUSERPROFILE
            public ArrayList getTotalTaskTime(string UserID, string DisplaySol)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("getUserProfileTotalTaskTimeAE", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                    cmd.Parameters.Add(new SqlParameter("@DisplaySol", DisplaySol));
                    rdr = cmd.ExecuteReader();
                    ArrayList values = new ArrayList();
                    while (rdr.Read())
                    {
                        values.Add(rdr["TotalTaskTime"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public ArrayList getTotalTaskTimeAll(string DisplaySol)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("getUserProfileTotalTaskTimeAEExperts", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@DisplaySol", DisplaySol));
                    rdr = cmd.ExecuteReader();
                    ArrayList values = new ArrayList();
                    while (rdr.Read())
                    {
                        values.Add(rdr["TotalTaskTime"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
        #endregion
        #region getDisplaySolutionUSERPROFILE
            public ArrayList getDisplaySolution(string UserID)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("getUserProfileDisplaySolution", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                    rdr = cmd.ExecuteReader();
                    ArrayList values = new ArrayList();
                    while (rdr.Read())
                    {
                        values.Add(rdr["DisplaySolutionScreen"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public int getNrRowsDisplaySolution(string UserID, string YN)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("getUserProfileDisplaySolutionCount", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                    cmd.Parameters.Add(new SqlParameter("@DisplaySolScreen", YN));
                    rdr = cmd.ExecuteReader();
                    int value = 0;
                    while (rdr.Read())
                    {
                        value = (int)rdr[0];
                    }
                    return value;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
        #endregion
        //Verify User
        public ArrayList getPassword(string param)
        {
            try
            {
                openConnection();
                SqlCommand cmd = new SqlCommand("VerifyUser", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@userID", param));
                rdr = cmd.ExecuteReader();
                ArrayList values = new ArrayList();
                while (rdr.Read())
                {
                    values.Add(rdr["Password"].ToString());
                    values.Add(rdr["UserID"].ToString());
                    values.Add(rdr["Firstname"].ToString());
                    values.Add(rdr["Lastname"].ToString());
                    values.Add(rdr["ExpLevel"].ToString());
                }
                return values;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
        //Call Log Stored Procedures
        #region
            public BindingList<string> getPriority()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetPriority", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["PriorityID"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public String getSeverity(int param)
            {
                try
                {
                    String severity = "";
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetSeverity", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Priority", param));
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        severity = rdr["PriorityName"].ToString();
                    }
                    return severity;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public BindingList<string> getServiceNames()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetServiceName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["ServiceName"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public BindingList<string> getCallSource()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetCallSource", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["CallSource"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public BindingList<string> getCallType(String param)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetCallType", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@sName", param));
                    rdr = cmd.ExecuteReader();
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["CallType"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public BindingList<string> getSubCallType(String param)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetSubCallType", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@callType", param));
                    rdr = cmd.ExecuteReader();
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["SubCallType"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public string[,] getCallTypeALL()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetCallTypeAll", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    int counter = 0;
                    string [,] row = new string[115,2];
                    while (rdr.Read())
                    {
                        row[counter,0] = rdr["CallType"].ToString();
                        row[counter, 1] = rdr["ServiceName"].ToString();
                        counter++;
                    }
                    return row;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public string[,] getSubCallTypeALL()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetSubCallTypeAll", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    int counter = 0;
                    string[,] row = new string[93, 2];
                    while (rdr.Read())
                    {
                        row[counter, 0] = rdr["SubCallType"].ToString();
                        row[counter, 1] = rdr["CallType"].ToString();
                        counter++;
                    }
                    return row;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public string check_If_CALLID_exists(string cID)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("check_IfCALLID_exists", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@CID", cID));
                    rdr = cmd.ExecuteReader();
                    string value = "";
                    while (rdr.Read())
                    {
                        value = rdr["CallID"].ToString();
                    }
                    return value;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public String getMAxCallID()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("getMaxCallID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    String value = "";
                    while (rdr.Read())
                    {
                        value = rdr[0].ToString();
                    }
                    return value;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            //Log a call
            public void LogACall(string cID, string date, string time,string userID, string custID, string sName, string cType, string scType, string status, string priority, string cSource, string cDescr)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("insertCall", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@cID", cID));
                    cmd.Parameters.Add(new SqlParameter("@date", date));
                    cmd.Parameters.Add(new SqlParameter("@time", time));
                    cmd.Parameters.Add(new SqlParameter("@userID", userID));
                    cmd.Parameters.Add(new SqlParameter("@customerID", custID));
                    cmd.Parameters.Add(new SqlParameter("@serviceName", sName));
                    cmd.Parameters.Add(new SqlParameter("@callType", cType));
                    cmd.Parameters.Add(new SqlParameter("@subCallType", scType));
                    cmd.Parameters.Add(new SqlParameter("@callStatus", status));
                    cmd.Parameters.Add(new SqlParameter("@priority", priority));
                    cmd.Parameters.Add(new SqlParameter("@callSource", cSource));
                    cmd.Parameters.Add(new SqlParameter("@callDescription", cDescr));
                      cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
        #endregion
        //Assign A Call
        #region
            public BindingList<string> getCampus()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetCampus", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["Campus"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            //gets all assigneeID's located on specified campus 
            public BindingList<string> getAssigneeID(string param)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetAssigneeID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@campus", param));
                    rdr = cmd.ExecuteReader();
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["AssigneeID"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            //gets all assignee details with specified assigneeID
            public ArrayList getAssigneeDetails(string param)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetAssigneeDetails", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@AID", param));
                    rdr = cmd.ExecuteReader();
                    ArrayList values = new ArrayList();
                    while (rdr.Read())
                    {
                        values.Add(rdr["AssigneeID"].ToString());
                        values.Add(rdr["FirstName"].ToString());
                        values.Add(rdr["Surname"].ToString());
                        values.Add(rdr["Campus"].ToString());
                        values.Add(rdr["GroupName"].ToString());
                        values.Add(rdr["Email"].ToString());
                        values.Add(rdr["Cell"].ToString());
                        values.Add(rdr["Extension"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public BindingList<string> getAssigneeIDAll()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("GetAssigneeIDAll", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    //int counter = 0;
                    string[,] row = new string[65, 2];
                    // Bronwins: OLD returnrd type: string[,]
                    //while (rdr.Read())
                    //{
                    //    row[counter, 0] = rdr["AssigneeID"].ToString();
                    //    row[counter, 1] = rdr["Campus"].ToString();
                    //    counter++;
                    //}
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["AssigneeID"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            //Assign a call
            public void AssignACall(string cID, string assignID, string gName, string aName, string date, string time, string comments)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("insertAssignment", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@cID", cID));
                    cmd.Parameters.Add(new SqlParameter("@AssignedID", assignID));
                    cmd.Parameters.Add(new SqlParameter("@GroupName", gName));
                    cmd.Parameters.Add(new SqlParameter("@AssigneeName", aName));
                    cmd.Parameters.Add(new SqlParameter("@Date", date));
                    cmd.Parameters.Add(new SqlParameter("@Time", time));
                    cmd.Parameters.Add(new SqlParameter("@comments", comments));
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public string check_If_CALLID2_exists(string cID)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("check_IfCALLID_exists2", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@CID", cID));
                    rdr = cmd.ExecuteReader();
                    string value = "";
                    while (rdr.Read())
                    {
                        value = rdr["CallID"].ToString();
                    }
                    return value;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
        #endregion
        //Provide Solution
        #region
            //get Call Cause
            public BindingList<string> getCause(string param)
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("getCallCause", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@sName", param));
                    rdr = cmd.ExecuteReader();
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["CallCause"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            //get How Resolved
            public BindingList<string> getResolvedHow()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("getResolvedHow", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    BindingList<string> values = new BindingList<string>();
                    while (rdr.Read())
                    {
                        values.Add(rdr["ResolveLocation"].ToString());
                    }
                    return values;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            public string[,] getCauseAll()
            {
                try
                {
                    openConnection();
                    SqlCommand cmd = new SqlCommand("getCallCauseAll", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    rdr = cmd.ExecuteReader();
                    int counter = 0;
                    string[,] row = new string[117, 2];
                    while (rdr.Read())
                    {
                        row[counter, 0] = rdr["CallCause"].ToString();
                        row[counter, 1] = rdr["ServiceName"].ToString();
                        counter++;
                    }
                    return row;
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
            }
            //Update Call Log with Solution Details
            public void updateCallLog(string cID, string cause, string resolveHow, string solDescr)
        {
            try
            {
                openConnection();
                SqlCommand cmd = new SqlCommand("updateCallLog", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@CID", cID));
                cmd.Parameters.Add(new SqlParameter("@SolCause", cause));
                cmd.Parameters.Add(new SqlParameter("@ResolveHow", resolveHow));
                cmd.Parameters.Add(new SqlParameter("@SolDescr", solDescr));
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
        #endregion
        #region EXPERT UI - RECENTLY&FREQUENTLY USED 
            #region RECENTLY
                public String getRecentlyUsedItem(string UserID, string listName)
                {
                    try
                    {
                        openConnection();
                        SqlCommand cmd = new SqlCommand("getRecentlyUsedItem", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                        cmd.Parameters.Add(new SqlParameter("@listName", listName));
                        rdr = cmd.ExecuteReader();
                        String value = "";
                        while (rdr.Read())
                        {
                            value = rdr["Recently"].ToString();
                        }
                        return value;
                    }
                    finally
                    {
                        if (con != null)
                        {
                            con.Close();
                        }
                        if (rdr != null)
                        {
                            rdr.Close();
                        }
                    }
                }
                public void updateRecentlyUsedItem(string UserID, string listName, string recentlyUsedItem)
                {
                    try
                    {
                        openConnection();
                        SqlCommand cmd = new SqlCommand("updateRecentlyUsedItem", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                        cmd.Parameters.Add(new SqlParameter("@listName", listName));
                        cmd.Parameters.Add(new SqlParameter("@recently", recentlyUsedItem));
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (con != null)
                        {
                            con.Close();
                        }
                        if (rdr != null)
                        {
                            rdr.Close();
                        }
                    }
                }
                public void insertRecentlyUsedItem(string UserID, string listName, string recentlyUsedItem)
                {
                    try
                    {
                        openConnection();
                        SqlCommand cmd = new SqlCommand("insertRecentlyUsedItem", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                        cmd.Parameters.Add(new SqlParameter("@listName", listName));
                        cmd.Parameters.Add(new SqlParameter("@recently", recentlyUsedItem));
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (con != null)
                        {
                            con.Close();
                        }
                        if (rdr != null)
                        {
                            rdr.Close();
                        }
                    }
                }
            #endregion
            #region FREQUENTLY
                public ArrayList getTop3FrequentlyUsedItems(string UserID, string listName)
                {
                    try
                    {
                        openConnection();
                        SqlCommand cmd = new SqlCommand("getTop3FrequentlyUsedItems", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@listName", listName));
                        cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                        rdr = cmd.ExecuteReader();
                        ArrayList values = new ArrayList();
                        while (rdr.Read())
                        {
                            values.Add(rdr["ItemName"].ToString());
                        }
                        return values;
                    }
                    finally
                    {
                        if (con != null)
                        {
                            con.Close();
                        }
                        if (rdr != null)
                        {
                            rdr.Close();
                        }
                    }
                }
                public int getFrequency(string UserID, string listName, string itemName)
                {
                    try
                    {
                        openConnection();
                        SqlCommand cmd = new SqlCommand("getFrequentlyUsedItem", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@listName", listName));
                        cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                        cmd.Parameters.Add(new SqlParameter("@itemName", itemName));
                        rdr = cmd.ExecuteReader();
                        int value = 0;
                        while (rdr.Read())
                        {
                            value = (int)rdr["Frequency"];
                        }
                        return value;
                    }
                    finally
                    {
                        if (con != null)
                        {
                            con.Close();
                        }
                        if (rdr != null)
                        {
                            rdr.Close();
                        }
                    }
                }
                public void updateFrequentlyUsedItem(string UserID, string listName, string itemName, int frequency)
                {
                    try
                    {
                        openConnection();
                        SqlCommand cmd = new SqlCommand("updateFrequentlyUsedItem", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                        cmd.Parameters.Add(new SqlParameter("@listName", listName));
                        cmd.Parameters.Add(new SqlParameter("@itemName", itemName));
                        cmd.Parameters.Add(new SqlParameter("@frequency", frequency));
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (con != null)
                        {
                            con.Close();
                        }
                        if (rdr != null)
                        {
                            rdr.Close();
                        }
                    }
                }
                public void insertFrequentlyUsedItem(string UserID, string listName, string itemName, int frequency)
                {
                    try
                    {
                        openConnection();
                        SqlCommand cmd = new SqlCommand("insertFrequentlyUsedItem", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@userID", UserID));
                        cmd.Parameters.Add(new SqlParameter("@listName", listName));
                        cmd.Parameters.Add(new SqlParameter("@itemName", itemName));
                        cmd.Parameters.Add(new SqlParameter("@frequency", frequency));
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (con != null)
                        {
                            con.Close();
                        }
                        if (rdr != null)
                        {
                            rdr.Close();
                        }
                    }
                }
            #endregion
        #endregion
    }
}
